Amazon Athena Partition Projectionを用いたHiveパーティションのパーティションプルーニングの自動化

Amazon Athena Partition Projectionを用いたHiveパーティションのパーティションプルーニングの自動化

#ビッグデータ
#ビジネス
#Amazon Athena
#AWS
石川覚

石川覚

facebook logohatena logotwitter logo
Clock Icon2020.07.28

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

Hiveパーティション（カラム名ありパーティション）は、データレイクで一般的なファイルレイアウトです。本日は、year、month、dayのパーティションキーのテーブルに対してPartition Projectionを用いたHiveパーティションのパーティションプルーニングを通して、Hiveパーティションのレイアウト、複数カラム、digits指定について解説します。

今回は、Partition Projectionの応用編になりますので、Partition Projectionに基本については、以下のブログをご覧ください。

Hiveパーティション（カラム名ありパーティション）とは

データファイルを日付などのキー情報に基づきフォルダ毎に分割することをパーティションと言います。そのフォルダ名にキーと値の情報を指定する方式をHiveパーティション（カラム名ありパーティション）と言います。

Hiveパーティションは、MSCK REPIR TABLEを実行するだけでパーティションを自動的に認識できるので、新規でデータレイクを作成する際に採用されることがよくあります。

year、month、dayのHiveパーティション構成

フォルダは、year、month、dayの順に階層的に格納されています。例えば、2014年1月1日のデータは、year=2014/month=01/day=01の下にデータファイルが格納されます。

order_hp
└── year=2014
     └── month=01
          ├── day=01
          │   └── 20200707_121540_061186_2027485_0003_part_00.parquet
          ├── day=02
          │   └── 20200707_121540_061186_2027485_0001_part_00.parquet
          ├── day=03
          │   └── 20200707_121540_061186_2027485_0003_part_00.parquet
          ├── day=04
          │   ├── 20200707_121540_061186_2027485_0001_part_00.parquet
          │   └── 20200707_121540_061186_2027485_0003_part_00.parquet
          ├── day=05
          │   └── 20200707_121540_061186_2027485_0003_part_00.parquet
          ：
          ：
          ：
          ├── day=29
          │   ├── 20200707_121540_061186_2027485_0001_part_00.parquet
          │   ├── 20200707_121540_061186_2027485_0002_part_00.parquet
          │   └── 20200707_121540_061186_2027485_0003_part_00.parquet
          └── day=31
              └── 20200707_121540_061186_2027485_0003_part_00.parquet

Partition Projectionの設定

Hiveパーティションのレイアウト、複数カラム、digits指定について解説します。

Hiveパーティションのレイアウトの指定

パーティションのパスは、year=2014/month=01/day=01のようになるため、TBLPROPERTIESのstorage.location.templateはyear=${year}/month=${month}/day=${day}のようにカラム名の「値」の箇所はプレースホルダに置き換えます。

よって、storage.location.templateは以下のように指定します。

'storage.location.template'='s3://<bucket_name>/order_hp/year=${year}/month=${month}/day=${day}',

複数カラムの指定

year=2014/month=01/day=01のように3つのカラムの値を自動的に生成するには、year、month、dayのそれぞれのデータ範囲（range）やデータ型（type）、増分（interval）を指定します。

-- year
'projection.day.interval'='1', 
'projection.day.range'='1,31', 
'projection.day.type'='integer', 
-- month
'projection.month.interval'='1', 
'projection.month.range'='1,12', 
'projection.month.type'='integer', 
-- day
'projection.year.interval'='1', 
'projection.year.range'='2014,2015', 
'projection.year.type'='integer',

digitsの指定

例えば、dayのデータ型にintegerを指定すると、1, 2, 3, 4, … 31と連続しますが、フォルダに指定した実際の値は、01, 02, 03, 04, … 31であるため、誤ったパーティションを設定し、データを参照できません。

そのため、数値の前に「0埋め」する指定するprojection.day.digitsに桁数を指定します。dayの場合は、01〜31の範囲なので、2を指定します。

-- year
'projection.day.digits'='2', 
'projection.day.interval'='1', 
'projection.day.range'='1,31', 
'projection.day.type'='integer', 
-- month
'projection.month.digits'='2', 
'projection.month.interval'='1', 
'projection.month.range'='1,12', 
'projection.month.type'='integer', 
-- day
'projection.year.digits'='4', 
'projection.year.interval'='1', 
'projection.year.range'='2014,2015', 
'projection.year.type'='integer',

全体のDDL

上記の全てを反映したDDLは、以下のとおりです。

CREATE EXTERNAL TABLE `order_hp`(
  `row_id` bigint, 
  `order_id` varchar(32), 
  `ship_date` varchar(10), 
  `ship_mode` varchar(64), 
  `customer_id` varchar(64), 
  `customer_name` varchar(64), 
  `segment` varchar(64), 
  `country` varchar(16), 
  `city` varchar(16), 
  `state` varchar(16), 
  `region` varchar(16), 
  `product_id` varchar(255), 
  `category` varchar(16), 
  `sub_category` varchar(32), 
  `product_name` varchar(255), 
  `sales` double, 
  `quantity` bigint, 
  `discount` double, 
  `profit` double)
PARTITIONED BY ( 
  `year` int, 
  `month` int, 
  `day` int)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://<bucket_name>/order_hp'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'projection.enabled'='true', 
  'projection.day.digits'='2', 
  'projection.day.interval'='1', 
  'projection.day.range'='1,31', 
  'projection.day.type'='integer', 
  'projection.month.digits'='2', 
  'projection.month.interval'='1', 
  'projection.month.range'='1,12', 
  'projection.month.type'='integer', 
  'projection.year.digits'='4', 
  'projection.year.interval'='1', 
  'projection.year.range'='2014,2017', 
  'projection.year.type'='integer', 
  'storage.location.template'='s3://<bucket_name>/order_hp/year=${year}/month=${month}/day=${day}', 
  'transient_lastDdlTime'='1595867108')

別解：enumの利用

今回はキーが連続する数値でしたが、enum（列挙型）に値を列挙することも可能です。以下の例では、あえてenum（列挙型）を用いると以下のように定義出来ます。enumの実際のユースケースとしては、都道府県のように連続していない値、かつ列挙可能なカーディナリティが低いカラムに適しています。

CREATE EXTERNAL TABLE `order_hp_2`(
  `row_id` bigint, 
  `order_id` varchar(32), 
  `ship_date` varchar(10), 
  `ship_mode` varchar(64), 
  `customer_id` varchar(64), 
  `customer_name` varchar(64), 
  `segment` varchar(64), 
  `country` varchar(16), 
  `city` varchar(16), 
  `state` varchar(16), 
  `region` varchar(16), 
  `product_id` varchar(255), 
  `category` varchar(16), 
  `sub_category` varchar(32), 
  `product_name` varchar(255), 
  `sales` double, 
  `quantity` bigint, 
  `discount` double, 
  `profit` double)
PARTITIONED BY ( 
  `year` int, 
  `month` int, 
  `day` int)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://<bucket_name>/order_hp'
TBLPROPERTIES (
  'has_encrypted_data'='false', 
  'projection.enabled'='true', 
  'projection.year.type' = 'enum',
  'projection.year.values' = '2014,2016,2017',
  'projection.month.type' = 'enum',
  'projection.month.values' = '01,02,03,04,05,06,07,08,09,10,11,12',
  'projection.day.type' = 'enum',
  'projection.day.values' = '01,02,03,04,05,06,07,08,09,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31',
  'storage.location.template'='s3://<bucket_name>/order_hp/year=${year}/month=${month}/day=${day}', 
  'transient_lastDdlTime'='1595867108')

最後

これまで新規でデータレイクを作成するとき、ファイルレイアウトは、Hiveパーティション（カラム名ありパーティション）に指定するように勧めてきた手前、Amazon Athena のPartition Projectionが利用できることを確認しました。ついでにenumの例も紹介しましたが、本来enumは数値のように連続していない値かつ列挙可能なカーディナリティが低いカラムに用いてください。

Share this article

facebook logohatena logotwitter logo

関連記事

【CDI】Informatica Cloud Data Integrationで、ソースのスキーマを自動的に読み込めるという「スキーマ変更処理」を検証してみた

【CDI】Informatica Cloud Data Integrationで、ソースのスキーマを自動的に読み込めるという「スキーマ変更処理」を検証してみた

User avatar
渡部晃季
2023.05.02
Redshiftで各テーブルのカラム毎の利用ブロック数を確認する

Redshiftで各テーブルのカラム毎の利用ブロック数を確認する

User avatar
k.uehara
2023.03.07
[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent

[レポート]AWSでデータメッシュアーキテクチャを構築する#ANT336 #reinvent

User avatar
nayu.t.s
2023.01.01
dbt Coalesce 2022と来年の告知

dbt Coalesce 2022と来年の告知

User avatar
ちゃあこ
2022.10.28
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.